﻿using System;
using System.Data;
using System.Data.SqlClient;

namespace HRMService
{
    public partial class MainService : ICandidate
    {
        public int Candidate_Using(int CandidateID)
        {
            int retVal = 0;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Candidate_Using", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@CandidateID", CandidateID);
            try
            {
                dbConn.Open();
                retVal = Convert.ToInt32(dbCmd.ExecuteScalar());
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public DataSet Candidate_GetList(out int ItemCount, int CR_CandidateID, string Status, string KeyWord, int currentPage, int pageSize)
        {
            /*
             - CR_CandidateID ở đây xét cho trạng thái tham gia phỏng vấn
                Nếu CR_CandidateID > 0 thì đã tham gia pv
                Nếu CR_CandidateID is null thì chưa tham gia pv
             - Status ở đây chỉ xét cho trạng thái trượt
                Nếu Status = "Truot" Thì lấy theo CR_Status = 3 or CR_Status = 5 or CR_Status = 7
                Nếu Status = "" Thì lấy tất không cần xét đến CR_Status
             */
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Candidate_GetList", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@CR_CandidateID", CR_CandidateID);
            dbCmd.Parameters.AddWithValue("@CR_Status", Status);
            dbCmd.Parameters.AddWithValue("@KeyWord", KeyWord);
            dbCmd.Parameters.AddWithValue("@CurrentPage", currentPage);
            dbCmd.Parameters.AddWithValue("@PageSize", pageSize);
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
                ItemCount = Convert.ToInt32(retVal.Tables[1].Rows[0][0]);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
            
            
        }

        public CandidateInfo Candidate_AutoCreateCode()
        {

            CandidateInfo retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Candidate_AutoCreateCode", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            SqlDataReader dr = null;

            try
            {
                dbConn.Open();
                dr = dbCmd.ExecuteReader();
                if (dr.Read())
                {
                    retVal = new CandidateInfo();
                    retVal.No = Convert.ToString(dr["Code"]);
                }
            }
            finally
            {
                if (dr != null) dr.Close();
                dbConn.Close();
            }
            return retVal;

        }

        public CandidateInfo Candidate_GetInfo(int CandidateID)
        {
          
            CandidateInfo retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Candidate_GetInfo", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Candidate_ID", CandidateID);
            SqlDataReader dr = null;

            try
            {
                dbConn.Open();
                dr = dbCmd.ExecuteReader();
                if (dr.Read())
                {
                    retVal = new CandidateInfo();
                    retVal.ID = Convert.ToInt32(dr["Candidate_ID"]);
                    if(dr["Candidate_PositionID"] != DBNull.Value)
                    retVal.PositionID = Convert.ToInt32(dr["Candidate_PositionID"]);
                    if (dr["Candidate_DepartmentID"] != DBNull.Value)
                    retVal.DepartmentID = Convert.ToInt32(dr["Candidate_DepartmentID"]);
                    retVal.No = Convert.ToString(dr["Candidate_No"]);
                    retVal.FirstName = Convert.ToString(dr["Candidate_FirstName"]);
                    retVal.LastName = Convert.ToString(dr["Candidate_LastName"]);
                    retVal.Alias = Convert.ToString(dr["Candidate_Alias"]);
                    retVal.Education = Convert.ToString(dr["Candidate_Education"]);
                    retVal.DateOfBirth = dr["Candidate_DateOfBirth"] == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(dr["Candidate_DateOfBirth"]);
                    retVal.PlaceOfBirth = Convert.ToString(dr["Candidate_PlaceOfBirth"]);
                    retVal.Address = Convert.ToString(dr["Candidate_Address"]);
                    retVal.Phone = Convert.ToString(dr["Candidate_Phone"]);
                    retVal.Sex = Convert.ToBoolean(dr["Candidate_Sex"]);
                    retVal.MaterialStatusID = Convert.ToInt32(dr["Candidate_MaterialStatusID"]);
                    retVal.ReligionID = Convert.ToInt32(dr["Candidate_ReligionID"]);
                    retVal.EthlicID = Convert.ToInt32(dr["Candidate_EthlicID"]);
                    retVal.IDCardNo = Convert.ToString(dr["Candidate_IDCardNo"]);
                    retVal.IDCardIssueDate = dr["Candidate_IDCardIssueDate"] == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(dr["Candidate_IDCardIssueDate"]);
                    retVal.IDCardIssueAuthority = Convert.ToString(dr["Candidate_IDCardIssueAuthority"]);
                    retVal.Date = dr["Candidate_Date"] == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(dr["Candidate_Date"]);
                    retVal.Description = Convert.ToString(dr["Candidate_Description"]);
                    retVal.TransportMeans = Convert.ToString(dr["Candidate_TransportMeans"]);
                    retVal.ResidentOfHaNoi = dr["Candidate_ResidentOfHaNoi"] == DBNull.Value ? 2 : Convert.ToInt32(dr["Candidate_ResidentOfHaNoi"]);
                    retVal.WhenAvailable = dr["Candidate_WhenAvailable"] == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(dr["Candidate_WhenAvailable"]);
                    retVal.MinSalaryExpected = dr["Candidate_MinSalaryExpected"] == DBNull.Value ? 0 : Convert.ToDecimal(dr["Candidate_MinSalaryExpected"]);
                    retVal.WorkOnShift = dr["Candidate_WorkOnShift"] == DBNull.Value ? 2 : Convert.ToInt32(dr["Candidate_WorkOnShift"]);
                    retVal.SpecialSkill = dr["Candidate_SpecialSkill"] == DBNull.Value ? "" : Convert.ToString(dr["Candidate_SpecialSkill"]);
                    retVal.Acquaintance = dr["Candidate_Acquaintance"] == DBNull.Value ? 2 : Convert.ToInt32(dr["Candidate_Acquaintance"]);
                    retVal.AcquaintanceName = dr["Candidate_AcquaintanceName"] == DBNull.Value ? "" : Convert.ToString(dr["Candidate_AcquaintanceName"]);
                    retVal.PartimeCourse = dr["Candidate_PartimeCourse"] == DBNull.Value ? 2 : Convert.ToInt32(dr["Candidate_PartimeCourse"]);
                    retVal.CourseName = dr["Candidate_CourseName"] == DBNull.Value ? "" : Convert.ToString(dr["Candidate_CourseName"]);
                    retVal.CourseTime = dr["Candidate_CourseTime"] == DBNull.Value ? 0 : Convert.ToInt32(dr["Candidate_CourseTime"]);
                    retVal.FinancialController = dr["Candidate_FinancialController"] == DBNull.Value ? "" : Convert.ToString(dr["Candidate_FinancialController"]);
                    retVal.SalaryDate = dr["Candidate_SalaryDate"] == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(dr["Candidate_SalaryDate"]);
                    retVal.ProbationPeriod = dr["Candidate_ProbationPeriod"] == DBNull.Value ? 0 : Convert.ToInt32(dr["Candidate_ProbationPeriod"]);
                    retVal.ProbationStartDate = dr["Candidate_ProbationStartDate"] == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(dr["Candidate_ProbationStartDate"]);
                    retVal.ProbationSalary = dr["Candidate_ProbationSalary"] == DBNull.Value ? 0 : Convert.ToDecimal(dr["Candidate_ProbationSalary"]);
                    retVal.TraningSalary = dr["Candidate_TraningSalary"] == DBNull.Value ? 0 : Convert.ToDecimal(dr["Candidate_TraningSalary"]);
                    retVal.ConfomationSalary = dr["Candidate_ConfomationSalary"] == DBNull.Value ? 0 : Convert.ToDecimal(dr["Candidate_ConfomationSalary"]);
                    retVal.Others = dr["Candidate_Others"] == DBNull.Value ? "" : Convert.ToString(dr["Candidate_Others"]);

                    retVal.CR_CandidateID = dr["CR_CandidateID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["CR_CandidateID"]);
                    

                }
            }
            finally
            {
                if (dr != null) dr.Close();
                dbConn.Close();
            }
            return retVal;

        }

        public int Candidate_Insert(CandidateInfo cInfo)
        {
            int retVal = 0;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Candidate_Insert", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;

            dbCmd.Parameters.AddWithValue("@Candidate_PositionID", cInfo.PositionID);
            dbCmd.Parameters.AddWithValue("@Candidate_DepartmentID", cInfo.DepartmentID);
            dbCmd.Parameters.AddWithValue("@Candidate_No", cInfo.No);
            dbCmd.Parameters.AddWithValue("@Candidate_FirstName", cInfo.FirstName);
            dbCmd.Parameters.AddWithValue("@Candidate_LastName", cInfo.LastName);
            dbCmd.Parameters.AddWithValue("@Candidate_Alias", cInfo.Alias);
            dbCmd.Parameters.AddWithValue("@Candidate_Education", cInfo.Education);
            dbCmd.Parameters.AddWithValue("@Candidate_DateOfBirth", cInfo.DateOfBirth);

            dbCmd.Parameters.AddWithValue("@Candidate_PlaceOfBirth", cInfo.PlaceOfBirth);
            dbCmd.Parameters.AddWithValue("@Candidate_Address", cInfo.Address);
            dbCmd.Parameters.AddWithValue("@Candidate_Phone", cInfo.Phone);
            dbCmd.Parameters.AddWithValue("@Candidate_Sex", cInfo.Sex);
            dbCmd.Parameters.AddWithValue("@Candidate_MaterialStatusID", cInfo.MaterialStatusID);
            dbCmd.Parameters.AddWithValue("@Candidate_ReligionID", cInfo.ReligionID);
            dbCmd.Parameters.AddWithValue("@Candidate_EthlicID", cInfo.EthlicID);
            dbCmd.Parameters.AddWithValue("@Candidate_IDCardNo", cInfo.IDCardNo);

            if (cInfo.IDCardIssueDate == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@Candidate_IDCardIssueDate", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@Candidate_IDCardIssueDate", cInfo.IDCardIssueDate);

            dbCmd.Parameters.AddWithValue("@Candidate_IDCardIssueAuthority", cInfo.IDCardIssueAuthority);

            if (cInfo.Date == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@Candidate_Date", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@Candidate_Date", cInfo.Date);

            

            dbCmd.Parameters.AddWithValue("@Candidate_TransportMeans", cInfo.TransportMeans);
            dbCmd.Parameters.AddWithValue("@Candidate_ResidentOfHaNoi", cInfo.ResidentOfHaNoi);
            dbCmd.Parameters.AddWithValue("@Candidate_Description", cInfo.Description);
            dbCmd.Parameters.AddWithValue("@Candidate_MinSalaryExpected", cInfo.MinSalaryExpected);
            dbCmd.Parameters.AddWithValue("@Candidate_WorkOnShift", cInfo.WorkOnShift);
            if (cInfo.WhenAvailable == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@Candidate_WhenAvailable", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@Candidate_WhenAvailable", cInfo.WhenAvailable);

            dbCmd.Parameters.AddWithValue("@Candidate_SpecialSkill", cInfo.SpecialSkill);
            dbCmd.Parameters.AddWithValue("@Candidate_Acquaintance", cInfo.Acquaintance);
            dbCmd.Parameters.AddWithValue("@Candidate_AcquaintanceName", cInfo.AcquaintanceName);
            dbCmd.Parameters.AddWithValue("@Candidate_PartimeCourse", cInfo.PartimeCourse);
            dbCmd.Parameters.AddWithValue("@Candidate_CourseName", cInfo.CourseName);
            dbCmd.Parameters.AddWithValue("@Candidate_CourseTime", cInfo.CourseTime);
            dbCmd.Parameters.AddWithValue("@Candidate_FinancialController", cInfo.FinancialController);
            if (cInfo.SalaryDate == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@Candidate_SalaryDate", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@Candidate_SalaryDate", cInfo.SalaryDate);

            if (cInfo.ProbationStartDate == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@Candidate_ProbationStartDate", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@Candidate_ProbationStartDate", cInfo.ProbationStartDate);
            dbCmd.Parameters.AddWithValue("@Candidate_ProbationPeriod", cInfo.ProbationPeriod);
            dbCmd.Parameters.AddWithValue("@Candidate_ProbationSalary", cInfo.ProbationSalary);
            dbCmd.Parameters.AddWithValue("@Candidate_TraningSalary", cInfo.TraningSalary);
            dbCmd.Parameters.AddWithValue("@Candidate_ConfomationSalary", cInfo.ConfomationSalary);
            dbCmd.Parameters.AddWithValue("@Candidate_Others", cInfo.Others);
            
            try
            {
                dbConn.Open();
                retVal = Convert.ToInt32(dbCmd.ExecuteScalar());
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public bool Candidate_Update(CandidateInfo cInfo)
        {
            bool retVal = false;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Candidate_Update", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;

            dbCmd.Parameters.AddWithValue("@Candidate_PositionID", cInfo.PositionID);
            dbCmd.Parameters.AddWithValue("@Candidate_DepartmentID", cInfo.DepartmentID);
            dbCmd.Parameters.AddWithValue("@Candidate_ID", cInfo.ID);
            dbCmd.Parameters.AddWithValue("@Candidate_No", cInfo.No);
            dbCmd.Parameters.AddWithValue("@Candidate_FirstName", cInfo.FirstName);
            dbCmd.Parameters.AddWithValue("@Candidate_LastName", cInfo.LastName);
            dbCmd.Parameters.AddWithValue("@Candidate_Alias", cInfo.Alias);
            dbCmd.Parameters.AddWithValue("@Candidate_Education", cInfo.Education);
            dbCmd.Parameters.AddWithValue("@Candidate_DateOfBirth", cInfo.DateOfBirth);
            dbCmd.Parameters.AddWithValue("@Candidate_PlaceOfBirth", cInfo.PlaceOfBirth);
            dbCmd.Parameters.AddWithValue("@Candidate_Address", cInfo.Address);
            dbCmd.Parameters.AddWithValue("@Candidate_Phone", cInfo.Phone);
            dbCmd.Parameters.AddWithValue("@Candidate_Sex", cInfo.Sex);
            dbCmd.Parameters.AddWithValue("@Candidate_MaterialStatusID", cInfo.MaterialStatusID);
            dbCmd.Parameters.AddWithValue("@Candidate_ReligionID", cInfo.ReligionID);
            dbCmd.Parameters.AddWithValue("@Candidate_EthlicID", cInfo.EthlicID);
            dbCmd.Parameters.AddWithValue("@Candidate_IDCardNo", cInfo.IDCardNo);

            if (cInfo.IDCardIssueDate == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@Candidate_IDCardIssueDate", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@Candidate_IDCardIssueDate", cInfo.IDCardIssueDate);

            dbCmd.Parameters.AddWithValue("@Candidate_IDCardIssueAuthority", cInfo.IDCardIssueAuthority);

            if (cInfo.Date == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@Candidate_Date", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@Candidate_Date", cInfo.Date);

            dbCmd.Parameters.AddWithValue("@Candidate_TransportMeans", cInfo.TransportMeans);
            dbCmd.Parameters.AddWithValue("@Candidate_ResidentOfHaNoi", cInfo.ResidentOfHaNoi);
            dbCmd.Parameters.AddWithValue("@Candidate_Description", cInfo.Description);

            dbCmd.Parameters.AddWithValue("@Candidate_MinSalaryExpected", cInfo.MinSalaryExpected);
            dbCmd.Parameters.AddWithValue("@Candidate_WorkOnShift", cInfo.WorkOnShift);
            if (cInfo.WhenAvailable == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@Candidate_WhenAvailable", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@Candidate_WhenAvailable", cInfo.WhenAvailable);


            dbCmd.Parameters.AddWithValue("@Candidate_SpecialSkill", cInfo.SpecialSkill);
            dbCmd.Parameters.AddWithValue("@Candidate_Acquaintance", cInfo.Acquaintance);
            dbCmd.Parameters.AddWithValue("@Candidate_AcquaintanceName", cInfo.AcquaintanceName);
            dbCmd.Parameters.AddWithValue("@Candidate_PartimeCourse", cInfo.PartimeCourse);
            dbCmd.Parameters.AddWithValue("@Candidate_CourseName", cInfo.CourseName);
            dbCmd.Parameters.AddWithValue("@Candidate_CourseTime", cInfo.CourseTime);
            dbCmd.Parameters.AddWithValue("@Candidate_FinancialController", cInfo.FinancialController);
            if (cInfo.SalaryDate == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@Candidate_SalaryDate", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@Candidate_SalaryDate", cInfo.SalaryDate);
            if (cInfo.ProbationStartDate == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@Candidate_ProbationStartDate", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@Candidate_ProbationStartDate", cInfo.ProbationStartDate);
            dbCmd.Parameters.AddWithValue("@Candidate_ProbationPeriod", cInfo.ProbationPeriod);
            dbCmd.Parameters.AddWithValue("@Candidate_ProbationSalary", cInfo.ProbationSalary);
            dbCmd.Parameters.AddWithValue("@Candidate_TraningSalary", cInfo.TraningSalary);
            dbCmd.Parameters.AddWithValue("@Candidate_ConfomationSalary", cInfo.ConfomationSalary);
            dbCmd.Parameters.AddWithValue("@Candidate_Others", cInfo.Others);

            try
            {
                dbConn.Open();
                if (dbCmd.ExecuteNonQuery() > 0)
                {
                    retVal = true;
                }
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public bool Candidate_Delete(int CandidateID)
        {
            bool retVal = false;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Candidate_Delete", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Candidate_ID", CandidateID);

            try
            {
                dbConn.Open();
                if (dbCmd.ExecuteNonQuery() > 0)
                {
                    retVal = true;
                }
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public bool Candidate_Exist(int _ID, string _No)
        {
            bool retVal = false;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Candidate_Exist", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Candidate_ID", _ID);
            dbCmd.Parameters.AddWithValue("@Candidate_No", _No);
            try
            {
                dbConn.Open();
                if (Convert.ToInt32(dbCmd.ExecuteScalar()) > 0)

                    retVal = true;
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
           
        }
    }
}
